﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;



namespace CheckClicker
{
    public partial class frmAdd : Form
    {
        #region "Biến cục bộ trong Form"

        private OleDbConnection objConnect;
        private DataTable objDataTable;
        private enum varMode : int { AddMode, EditMode, DeleteMode }
        #endregion

        #region "Tạo Kết nối và hủy kết nối"
        
        private void subCreateConnect()
        {
            String varChuoiConnect = "Provider = Microsoft.Jet.OLEDB.4.0 ;" +
                        "Data Source =" + Application.StartupPath + @"\Database.mdb;" +
                        "User ID = Admin";
            objConnect = new OleDbConnection(varChuoiConnect);
            objConnect.Open(); //Mở kết nối
        }
        
        private void subDestroyConnect()
        {
            objConnect.Close(); //Đóng kết nối
            objConnect.Dispose();//Giải phóng tài nguyên
            objConnect = null; //Hủy đối tượng
        }
        #endregion
        #region "Add du lieu"
        private void subInsertByAdapterCommandBuilder()
        {
            //Tạo kết nối tới file Access
            subCreateConnect();
            //Nạp dữ liệu vào DataTable
            objDataTable = new DataTable("Abc");
            //Tạo đối tượng Adapter và Fill vào DataTable
            OleDbDataAdapter objAdapter = new OleDbDataAdapter("select * from NewID", objConnect);
            objAdapter.Fill(objDataTable);
            //Tạo command cho Adapter
            new OleDbCommandBuilder(objAdapter);
            //Insert dữ liệu vào DataTable
            DataRow objRow = objDataTable.NewRow();
            objRow["ID"] = txtID.Text;
            objRow["Class"] = cbrClass.Text;
            objRow["Name"] = txtName.Text;
            objDataTable.Rows.Add(objRow);
            //Insert dữ liệu vào nguồn
            objAdapter.Update(objDataTable);
            //Hủy đối tượng
            subDestroyConnect();
        }
        #endregion
        // Truy xuat du lieu
        private void subSelectByDataAdapter()
        {
            //Tạo kết nối tới file Access
            subCreateConnect();
            //Tao Command
            OleDbCommand objCommand = new OleDbCommand();
            objCommand.Connection = objConnect;
            objCommand.CommandType = CommandType.Text;
            objCommand.CommandText = "Select * From NewID";
            //Taoj đối tượng Adapter
            OleDbDataAdapter objAdapter = new OleDbDataAdapter();
            objAdapter.SelectCommand = objCommand;
            //Tạo DataTable nhận dữ liệu trả về
            objDataTable = new DataTable("abc");
            objAdapter.Fill(objDataTable);
            //Gán dữ liệu vào Datagrid
            DGAccess.DataSource = objDataTable;
            //Hủy các đối tượng
            objCommand.Dispose();
            objCommand = null;
            objDataTable.Dispose();
            objDataTable = null;
            objAdapter.Dispose();
            objAdapter = null;
            subDestroyConnect();
        }
        #region "Load du lieu"
        private void LoadData()
        {
            //Tạo kết nối tới file Access
            subCreateConnect();
            //Truy vấn dữ liệu
            String varSelect = "Select * from NewID";//Lệnh SQL
            OleDbCommand objCommand = new OleDbCommand(varSelect, objConnect);
            //Tạo DataReader nhận dữ liệu trả về
            OleDbDataReader objReader = objCommand.ExecuteReader();
            //Tạo đối tượng DataTable và Load DataReader vào
            objDataTable = new DataTable("abc");
            objDataTable.Load(objReader);
            //Gán dữ liệu vào Datagrid
            DGAccess.DataSource = objDataTable;
            //Hủy các đối tượng
            objCommand.Dispose();
            objCommand = null;
            objReader.Close();
            objReader.Dispose();
            objReader = null;
            objDataTable.Dispose();
            objDataTable = null;
            subDestroyConnect();
        }

        #endregion

        #region "Delete with ID"
        private void subDeleteCommandNoParameter()
        {
            //Tạo kết nối
            subCreateConnect();
            //Tạo command
            OleDbCommand objcommand = new OleDbCommand();
            objcommand.Connection = objConnect;
            objcommand.CommandType = CommandType.Text;
            objcommand.CommandText = "Delete From NewID Where ID = ?";
            objcommand.Parameters.Add("ID", OleDbType.Char).Value = txtID.Text;
            objcommand.ExecuteNonQuery();
            //Hủy đối tượng
            objcommand.Dispose(); objcommand = null;
            subDestroyConnect();
        }
        #endregion

        #region "Delete datatable"
        private void deletetable()
        {
            //Tạo kết nối
            subCreateConnect();
            //Tạo command
            OleDbCommand objcommand = new OleDbCommand();
            objcommand.Connection = objConnect;
            objcommand.CommandType = CommandType.Text;
            objcommand.CommandText = "Delete From NewID";
            objcommand.ExecuteNonQuery();
            //Hủy đối tượng
            objcommand.Dispose(); objcommand = null;
            subDestroyConnect();
        }
        #endregion

        #region "Find data"
        private void findData()
        {
            //Tạo kết nối tới file Access
            subCreateConnect();
            //Tao Command
            OleDbCommand objCommand = new OleDbCommand();
            objCommand.Connection = objConnect;
            objCommand.CommandType = CommandType.Text;
            objCommand.CommandText = "Select * From NewID where ID=@ID";
            objCommand.Parameters.Add("@ID", OleDbType.Char).Value = txtID.Text;
            //Taoj đối tượng Adapter
            OleDbDataAdapter objAdapter = new OleDbDataAdapter();
            objAdapter.SelectCommand = objCommand;
            //Tạo DataTable nhận dữ liệu trả về
            objDataTable = new DataTable("abc");
            objAdapter.Fill(objDataTable);
            DataRow row = objDataTable.Rows[0];
            cbrClass.Text = row["Class"].ToString();
            txtName.Text = row["Name"].ToString();
            //Hủy các đối tượng
            objCommand.Dispose();
            objCommand = null;
            objDataTable.Dispose();
            objDataTable = null;
            objAdapter.Dispose();
            objAdapter = null;
            subDestroyConnect();

        }
        #endregion
        #region "Update data"
        private void subUpdateCommandHasParameter()
        {
            //Tạo kết nối tới file Access
            subCreateConnect();
            //Nạp dữ liệu vào DataTable
            objDataTable = new DataTable("Abc");
            //Tạo đối tượng Adapter và Fill vào DataTable
            OleDbDataAdapter objAdapter = new OleDbDataAdapter("select * from NewID", objConnect);
            objAdapter.Fill(objDataTable);
            //Update dữ liệu vào DataTable                
            //Chọn ra dòng cần chỉnh sửa dữ liệu
            DataRow[] objRow = objDataTable.Select("ID = " + txtID.Text);
            objRow[0]["Class"] = cbrClass.Text;
            objRow[0]["Name"] = txtName.Text;
           
            //Tạo đối tượng Command
            OleDbCommand objCommand = new OleDbCommand();
            objCommand.Connection = objConnect;
            objCommand.CommandType = CommandType.Text;
            objCommand.CommandText = "Update NewID " +
                                     "Set Class=@Class, Name=@Name " +
                                     "Where ID=@ID";
            objCommand.Parameters.Add("@Class", OleDbType.VarChar, 20, "Class");
            objCommand.Parameters.Add("@Name", OleDbType.VarChar, 20, "Name");
            objCommand.Parameters.Add("@ID", OleDbType.VarChar, 20, "ID");
            //Update dữ liệu nguồn
            objAdapter.UpdateCommand = objCommand;
            objAdapter.Update(objDataTable);
            //Hủy đối tượng
            objAdapter.Dispose();
            objAdapter = null;
            objDataTable.Dispose();
            objDataTable = null;
            objCommand.Dispose();
            objCommand = null;
            subDestroyConnect();
        }
        #endregion

        #region "Xuat file ra Exel"
        private void Export()
        {
            try
            {
                Excel.Application excel = new Excel.Application();
                excel.Application.Workbooks.Add(true);

                Excel.Application oExcel_12 = null;                //Excel_12 Application
                Excel.Workbook oBook = null;                       // Excel_12 Workbook
                Excel.Sheets oSheetsColl = null;                   // Excel_12 Worksheets collection
                Excel.Worksheet oSheet = null;                     // Excel_12 Worksheet
                Excel.Range oRange = null;                         // Cell or Range in worksheet
                Object oMissing = System.Reflection.Missing.Value;
                oExcel_12 = new Excel.Application();
                // Make Excel_12 visible to the user.
                oExcel_12.Visible = true;
                // Set the UserControl property so Excel_12 won't shut down.
                oExcel_12.UserControl = true;
                // System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-US");
                // Add a workbook.
                oBook = oExcel_12.Workbooks.Add(oMissing);
                // Get worksheets collection
                oSheetsColl = oExcel_12.Worksheets;

                oSheet = (Excel.Worksheet)oSheetsColl.get_Item("Sheet1");

                for (int j = 0; j < DGAccess.Columns.Count; j++)
                {
                    oRange = (Excel.Range)oSheet.Cells[1, j + 1];
                    oRange.Value2 = DGAccess.Columns[j].HeaderText;
                }

                for (int i = 0; i < DGAccess.Rows.Count; i++)
                {
                    for (int j = 0; j < DGAccess.Columns.Count; j++)
                    {

                        oRange = (Excel.Range)oSheet.Cells[i + 2, j + 1];
                        oRange.Value2 = DGAccess.Rows[i].Cells[j].Value;
                    }
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        #endregion

        public frmAdd()
        {
            InitializeComponent();
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            //Export();
            this.Hide();
            frmMain frm = new frmMain();
            frm.ShowDialog();
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            try
            {
                subInsertByAdapterCommandBuilder();
                LoadData();
            }
            catch (OleDbException Exception)
            {
                String a = Convert.ToString(Exception.ErrorCode);
                MessageBox.Show(a);
                //throw ;

            }
        }

        private void frmAdd_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'databaseDataSet1.NewID' table. You can move, or remove it, as needed.
            this.newIDTableAdapter.Fill(this.databaseDataSet1.NewID);
            // TODO: This line of code loads data into the 'databaseDataSet1.NewID' table. You can move, or remove it, as needed.
            //this.newIDTableAdapter.Fill(this.databaseDataSet.NewID);
            // TODO: This line of code loads data into the 'databaseDataSet3.NewID' table. You can move, or remove it, as needed.
            // TODO: This line of code loads data into the 'databaseDataSet1.NewID' table. You can move, or remove it, as needed.
            //this.newIDTableAdapter.Fill(this.databaseDataSet.NewID);
            LoadData();
        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            this.Hide();
            frmMain frm = new frmMain();
            frm.ShowDialog();
        }

        private void btnDel_Click(object sender, EventArgs e)
        {
            subDeleteCommandNoParameter();
            LoadData();
        }

        private void btnFind_Click(object sender, EventArgs e)
        {
            findData();
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            subUpdateCommandHasParameter();
            LoadData();
        }

        private void btnReset_Click(object sender, EventArgs e)
        {
            deletetable();
            LoadData();
        }

        private void Add_KeyDown(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == 13)
            {
                btnAdd_Click(null, null);
            }
        }
        
       
    }
}
